My self is Mahendra D Rathod.

I have some problem in executing trigger at backend side

One trigger is fired AFTER INSERT OR UPDATE ON mtrl_issue_detail

So when any row of "mtrl_issue_detail" is inserted or updated this trigger is fired on one function.

This function updates the same row that has just been inserted or updated.

Now when this process happens the database gets HANGED. and i have to restart the database on server.

Is there any solution to do this process without affecting my server.

********************************************************************************

FUNCTION: taxcal_fun()

********************************************************************************

CREATE FUNCTION taxcal_fun() returns opaque as'

DECLARE

rec RECORD;

tmp_rate REAL;

bl_amt REAL;

ex_amt REAL;

ad_amt REAL;

les_amt REAL;

t_amt REAL;

rel_per REAL;

rel_frac REAL;

tx_amt REAL;

ad_tax REAL;

gr_amt REAL;

BEGIN

SELECT INTO rec * FROM rate_mst r, mtrl_issue_mst m

WHERE r.m_code = NEW.mtrl_code

AND m.issue_no = NEW.issue_no

AND r.effective_dt = (SELECT MAX(effective_dt) FROM rate_mst

WHERE rate_mst.m_code=NEW.mtrl_code

AND rate_mst.effective_dt <= m.issue_dt);

IF rec.issue_type = ''stk_trf_sale'' THEN

tmp_rate := rec.trf_rate;

ELSIF rec.issue_type = ''con_sale'' THEN

tmp_rate := rec.con_rate;

ELSIF rec.issue_type = ''gen_sale'' THEN

tmp_rate := rec.gen_rate_cs;

ELSIF rec.issue_type = ''agent_sale'' THEN

tmp_rate := rec.soc_rate_cs;

ELSE rec.issue_type = ''mrp_sale'' THEN

tmp_rate := rec.mrp_rate_cs;

END IF;

IF rec.tax_code = ''c'' OR rec.tax_code = ''C'' THEN

tmp_rate := tmp_rate + CAST(round(tmp_rate * rec.cst_per / 100,2) AS REAL);

rel_per := rec.cst_per;

rel_frac := rec.cst_frac;

ELSIF rec.tax_code = ''g'' or rec.tax_code = ''G'' THEN

tmp_rate := tmp_rate + CAST(round(tmp_rate * rec.gst_per / 100,2) AS REAL);

rel_per := rec.gst_per;

rel_frac := rec.gst_frac;

ELSE

tmp_rate := tmp_rate;

rel_per := 0;

rel_frac := 0;

END IF;

bl_amt := CAST((NEW.issueqty_cs * tmp_rate) AS REAL);

ex_amt:= bl_amt*(rec.exise_per/100)*(rec.exise_frac/100);

ad_amt := NEW.issueqty_cs * rec.add_other_cs;

les_amt := cast((NEW.issueqty_mt * 1000 * rec.less_other_kg) AS REAL);

t_amt := CAST((bl_amt + ex_amt + ad_amt - les_amt) AS REAL);

tx_amt := t_amt * (rel_per/100) * (rel_frac/100);

ad_tax := tx_amt * (rec.additional_tax/100) * (rec.additional_frac/100);

gr_amt := t_amt + tx_amt + ad_tax;

UPDATE mtrl_issue_detail

SET rate_per_cs=tmp_rate,

bill_amt=bl_amt,

exise_amt=ex_amt,

add_amt=ad_amt,

less_amt=les_amt,

tot_amt=t_amt,

tax_amt=tx_amt,

add_tax=ad_tax,

gross_amt=gr_amt

WHERE issue_no=NEW.issue_no

AND mtrl_code=NEW.mtrl_code

AND batch_code=NEW.batch_code;

RETURN NULL;

END;'

LANGUAGE 'plpgsql';

********************************************************************************

TRIGGER : taxcal_trg

********************************************************************************

CREATE TRIGGER taxcal_trg

AFTER INSERT OR UPDATE ON mtrl_issue_detail

FOR EACH ROW EXECUTE PROCEDURE taxcal_fun();

Whe the BLUE part is executed the problem arises

and without this part is runs smoothly.

Is there any solution update this query.